if exists (select 1 from sysobjects where name = 'upd_dienstserie' and type = 'P')
begin
   drop procedure upd_dienstserie
   print 'Procedure: upd_dienstserie deleted ...'
end
go
create procedure upd_dienstserie(
  @OldPrsID             int           = 1,
  @Olddienstid          int           = 1,
  @OldAbteilungID       int           = 1,
  @OldTag               int           = 1,
  @OldTyp               varchar(5)       = 'W',--M: Monat W: Woche
  @Oldvon               varchar(8)    = '20080101',
  @Oldbis               varchar(8)    = '20100101',
  @PrsID             int           = 1,
  @dienstid          int           = 1,
  @AbteilungID       int           = 1,
  @Tag               int           = 1,
  @Typ               varchar(5)       = 'W',--M: Monat W: Woche
  @von               varchar(8)    = '20080101',
  @bis               varchar(8)    = '20100101'
)
as
begin
  set nocount on

  declare @datevon datetime
  declare @datebis datetime
  declare @olddatevon datetime
  declare @olddatebis datetime

  select @datevon = convert(datetime, @von, 112)
  select @datebis = convert(datetime, @bis, 112)
  select @olddatevon = convert(datetime, @Oldvon, 112)
  select @olddatebis = convert(datetime, @Oldbis, 112)

  update DienstSerie 
     set PrsID = @PrsID, 
         DienstID = @dienstid, 
         AbteilungID = @AbteilungID, 
         Tag = @Tag, 
         Typ = upper(@Typ), 
         Von = @datevon, 
         bis = @datebis
   where PrsID = @OldPrsID
     and DienstID = @Olddienstid
     and AbteilungID = @OldAbteilungID
     and Tag = @OldTag
     and Typ = upper(@OldTyp)
     and Von = @olddatevon
     and bis = @olddatebis

end
go
print 'Procedure: upd_dienstserie done ...'
go
grant exec on upd_dienstserie to prsadmins with grant option
go
grant exec on upd_dienstserie to prsusers
go

